﻿using System;
using System.Collections.Generic;
using System.Text;
using Gencode.Common.SQL;
using System.Data;
using Gencode.PrsCommon.App;

namespace Gencode.PrsCommon.Data.Plan
{
    public enum PlanStatus { Open, Closed };
    public class PlanData
    {
        /// <summary>
        /// Creates a list of every person in a plan. 
        /// prsID,name,nachname,abteilungid,abteilungname,prsschemaid,von,bis,rgb
        /// </summary>
        /// <param name="planID">PlanID Attribut</param>
        /// <returns>prsID,name,nachname,abteilungid,abteilungname,prsschemaid,von,bis,rgb,pensum</returns>
        public static DataView GetPersonenInPlan(int planID)
        {
            Querry query = new Querry();
            query.SetSTPName("get_personeninplan");
            query.AddParameter("@planid", planID);

            return query.ExecuteForDataView();
        }

        /// <summary>
        /// Creates a DataView with specific Data of a row. datum,name,dienstid,stunden,rgb
        /// </summary>
        /// <param name="planID">PlanID</param>
        /// <param name="prsSchemaID">PrsSchemaID</param>
        /// <returns>datum,name,dienstid,stunden,rgb</returns>
        public static DataView GetPlanRow(int planID, int prsSchemaID, int prsid)
        {
            Querry query = new Querry();
            query.SetSTPName("get_planrow");
            query.AddParameter("@panid", planID);
            query.AddParameter("@prsschemaid", prsSchemaID);
            query.AddParameter("@prsid", prsid);
            return query.ExecuteForDataView();
        }

        /// <summary>
        /// datum,rgb,prsid
        /// </summary>
        /// <param name="planID">PlanID</param>
        /// <param name="prsSchemaID">PrsSchemaID</param>
        /// <returns>datum,rgb,prsid</returns>
        public static DataView GetPlanRowColors(int planID, int prsSchemaID)
        {
            Querry query = new Querry();
            query.SetSTPName("get_planrowcolor");
            query.AddParameter("@planid", planID);
            query.AddParameter("@prsschemaid", prsSchemaID);
            return query.ExecuteForDataView();
        }

        /// <summary>
        /// Returns a DataView with the active Plannames
        /// </summary>
        /// <param name="mandid">mandid</param>
        /// <param name="addEmptyRow">Add a empty row at top</param>
        /// <returns>Name, PlanID</returns>
        public static DataView GetActivePlannames(string mandid, bool addEmptyRow)
        {
            DataView dv = SimpleQuerry.ExecuteForDataView("select Name, PlanID from ArbeitsPlan where Status = 1 and mandid = '" + mandid + "'");

            if (addEmptyRow)
            {
                DataRow row = dv.Table.NewRow();
                dv.Table.Rows.InsertAt(row, 0);
            }

            return dv;
        }

		/// <summary>
		/// Returns a DataView with the inactive Plannames [Name,PlanID]
		/// </summary>
		/// <param name="mandid">mandid</param>
		/// <param name="addEmptyRow">Add a empty row at top</param>
		/// <returns>Name,PlanID</returns>
		public static DataView GetInactivePlannames(string mandid, bool addEmptyRow)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select Name, PlanID from ArbeitsPlan where Status = 2 and mandid = '" + mandid + "'");

			if (addEmptyRow)
			{
				DataRow row = dv.Table.NewRow();
				dv.Table.Rows.InsertAt(row, 0);
			}

			return dv;
		}

        public static string GetPlanName(int planID)
        {
            DataView dv = SimpleQuerry.ExecuteForDataView("select Name from ArbeitsPlan where PlanID = " + planID);
            string tmp = dv[0].Row["Name"].ToString();
            return tmp;
        }

		public static int GetPlanSchemaID(int planid)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select SchemaID from ArbeitsPlan where PlanID = " + planid);
			int tmp = int.Parse(dv[0].Row[0].ToString());
			dv.Dispose();
			return tmp;
		}

		public static string GetSchemaName(int schemaid)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select Name from Schemas where SchemaID = " + schemaid);
			if (dv.Count > 0)
				return dv[0].Row[0].ToString();

			return string.Empty;
		}

        /// <summary>
        /// Creates a DataView with Schemas. [schemaid,name,status]
        /// </summary>
        /// <param name="mandid">mandid</param>
        /// <param name="onlyActive">get only active?</param>
        /// <returns>[schemaid,name,status]</returns>
        public static DataView GetSchemas(string mandid, bool onlyActive)
        {
            int status = 1;
            if (!onlyActive)
                status = 0;

            Querry query = new Querry();
            query.SetSTPName("get_shemas");
            query.AddParameter("@mandid", mandid);
            query.AddParameter("@status", status);
            return query.ExecuteForDataView();
        }

		/// <summary>
		/// Deactivate = 0; Activate = 1;
		/// </summary>
		/// <param name="schemaid"></param>
		/// <param name="status"></param>
		public static void UpdateSchemaStatus(int schemaid, int status)
		{
			SimpleQuerry.Execute("update Schemas set Status = " + status + " where SchemaID = " + schemaid);
		}

		/// <summary>
		/// [abteilungid,name]
		/// </summary>
		/// <param name="schemaid">schemaid</param>
		/// <returns>[abteilungid,name]</returns>
		public static DataView GetAbteilungenInSchema(int schemaid)
		{
			Querry query = new Querry("get_abteilungeninschema");
			query.AddParameter("@schemaid", schemaid);
			return query.ExecuteForDataView();
		}

		/// <summary>
		/// [prsid,anzeigename,vorname,nachname]
		/// </summary>
		/// <param name="abteilungid">abteilungid</param>
		/// <param name="schemaid">schemaid</param>
		/// <returns>[prsid,anzeigename,vorname,nachname]</returns>
		public static DataView GetPersonenInAbteilungAndSchema(int abteilungid, int schemaid)
		{
			Querry query = new Querry("get_personeninabteilunginschema");
			query.AddParameter("@abteilungid", abteilungid);
			query.AddParameter("@schemaid", schemaid);
			return query.ExecuteForDataView();
		}

		/// <summary>
		/// [prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]
		/// </summary>
		/// <param name="abteilungid">abteilungid</param>
		/// <returns>[prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]</returns>
		public static DataView GetPersonenInAbteilung(int abteilungid)
		{
			Querry query = new Querry("get_personeninabteilung");
			query.AddParameter("@abteilungid", abteilungid);
			return query.ExecuteForDataView();
		}

        /// <summary>
        /// [vortrag,soll,haben,ferien,anpassung]
        /// </summary>
        /// <param name="prsid"></param>
        /// <param name="abteilungid"></param>
        /// <param name="planid"></param>
        /// <returns></returns>
        public static DataView GetSaldo(int prsid, int abteilungid, int planid)
        {
            Querry query = new Querry();
            query.SetSTPName("get_saldo");
            query.AddParameter("@prsid", prsid);
            query.AddParameter("@planid", planid);
            query.AddParameter("@abteilungid", abteilungid);

            return query.ExecuteForDataView();
        }

		/// <summary>
		/// [saldoid,datum,grund,stunden]
		/// </summary>
		/// <param name="prsid">prsid</param>
		/// <param name="abteilungid">abteilungid</param>
		/// <param name="planid">planid</param>
		/// <returns>[saldoid,datum,grund,stunden]</returns>
		public static DataView GetSaldoAnpassung(int prsid, int abteilungid, int planid)
		{
			Querry query = new Querry("get_saldoanpassung");
			query.AddParameter("@prsid", prsid);
			query.AddParameter("@abteilungid", abteilungid);
			query.AddParameter("@planid", planid);
			return query.ExecuteForDataView();
		}

		public static void SetSaldoAnpassung(int prsid, int abteilungid, int planid, string datum, string grund, double stunden)
		{
			Querry query = new Querry("set_saldoanpassung");

			query.AddParameter("@PlanID", planid);
			query.AddParameter("@abteilungid", abteilungid);
			query.AddParameter("@PrsID", prsid);
			query.AddParameter("@Datum", DateUtil.ConvertDateToSQL(datum));
			query.AddParameter("@Grund", grund);
			query.AddParameter("@Stunden", stunden);

			query.Execute(true);
		}

        public static void DeleteSaldoAnpassung(int saldoid)
        {
            SimpleQuerry.Execute("delete from SaldoAnpassung where SaldoID = " + saldoid);
        }

        /// <summary>
        /// Updates the Status of a plan
        /// </summary>
        /// <param name="planID">PlanID</param>
        /// <param name="status">Status</param>
        public static void UpdatePlanStatus(int planID, PlanStatus status)
        {
            int st = 1;
            switch (status)
            {
                case PlanStatus.Open:
                    st = 1;
                    break;
                case PlanStatus.Closed:
                    st = 2;
                    break;
            }
            Querry query = new Querry();

            query.SetSTPName("upd_planstatus");

            query.AddParameter("@statusin", st);
            query.AddParameter("@planidin", planID);

            query.Execute(true);
        }

        public static void DeletePlan(int planID)
        {
            Querry query = new Querry();

            query.SetSTPName("del_plan");

            query.AddParameter("@planid", planID);

            query.Execute(true);
        }

        /// <summary>
		/// Creates a DataView with Schemadata. [schemaname,schemaid,prsschemaid,prsid,abteilungid,rgb,von,bis]
		/// </summary>
		/// <param name="planid">planid</param>
		/// <returns>[schemaname,schemaid,prsschemaid,prsid,abteilungid,abteilungname,rgb,von,bis]</returns>
		public static DataView GetPlanSchema(int schemaid)
		{
			Querry query = new Querry();
			query.SetSTPName("get_planshema");
			query.AddParameter("@schemaid", schemaid);
			return query.ExecuteForDataView();
		}

        /// <summary>
        /// Deletes all rows from a schema
        /// </summary>
        /// <param name="schemaid">schemaid</param>
        public static void DeleteRowsFromSchema(int schemaid)
        {
            SimpleQuerry.Execute("delete from PlanSchema where SchemaID = " + schemaid);
        }

		/// <summary>
		/// [schemaid]
		/// </summary>
		/// <param name="name">name</param>
		/// <param name="mandid">mandid</param>
		/// <returns>[schemaid]</returns>
		public static int CreateNewSchema(string name, string mandid)
		{
			Querry query = new Querry("set_schemas");
			query.AddParameter("@name", name);
			query.AddParameter("@mandid", mandid);
			DataView dv = query.ExecuteForDataView(true);

			if (dv.Count > 0)
				return int.Parse(dv[0].Row[0].ToString());

			return 0;
		}

        /// <summary>
        /// Saves a row in a Planschema
        /// </summary>
        /// <param name="schemaid">schemaid</param>
        /// <param name="row">prsschemaid</param>
        /// <param name="prsid">prsid</param>
        /// <param name="abteilungid">abteilungid</param>
        /// <param name="rgb">rgb of row</param>
        public static void SaveSchemaRow(int schemaid, int row, int prsid, int abteilungid, int rgb)
        {
            Querry query = new Querry();
            query.SetSTPName("set_planschema");
            query.AddParameter("@SchemaID", schemaid);
            query.AddParameter("@PrsSchemaID", row);
            query.AddParameter("@PrsID", prsid);
            query.AddParameter("@AbteilungID", abteilungid);
            query.AddParameter("@RGB", rgb);
            query.AddParameter("@ColorName", "");
            query.AddParameter("@Bezeichner", "");

            query.Execute(false);
        }

        /// <summary>
        /// Checks if a person already exists in a plan and abteilung
        /// </summary>
        /// <param name="prsid">prsid</param>
        /// <param name="abteilungid">abteilungid</param>
        /// <param name="planid">planid</param>
        /// <returns>true if person exists</returns>
        public static bool CheckIfPersonExistsInPlan(int prsid, int abteilungid, int planid)
        {
            DataView dv = SimpleQuerry.ExecuteForDataView("select * from Planschema ps, ArbeitsPlan ap where ps.SchemaID = ap.SchemaID and ap.Planid = " + planid 
                + " and ps.PrsID = " + prsid 
                + " and ps.AbteilungID = " + abteilungid);

            if (dv.Count > 0)
                return true;

            return false;
        }

		/// <summary>
		/// Checks if a person already exists in a schema and abteilung. true if person exists.
		/// </summary>
		/// <param name="prsid">prsid</param>
		/// <param name="abteilungid">abteilungid</param>
		/// <param name="planid">schemaid</param>
		/// <returns>true if person exists</returns>
		public static bool CheckIfPersonExistsInSchema(int prsid, int abteilungid, int schemaid)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select * from Planschema ps where ps.SchemaID = "+schemaid
				+ " and ps.PrsID = " + prsid
				+ " and ps.AbteilungID = " + abteilungid);

			if (dv.Count > 0)
				return true;

			return false;
		}

        /// <summary>
        /// Add an additional person into the plan (+schema)
        /// </summary>
        /// <param name="planid">planid</param>
        /// <param name="prsid">prsid</param>
        /// <param name="abteilungid">abteilungid</param>
        public static void SetAdditionalPersonInPlan(int planid, int prsid, int abteilungid)
        {
            Querry query = new Querry();
            query.SetSTPName("set_zusatzpersonplan");
            query.AddParameter("@planid", planid);
            query.AddParameter("@prsid", prsid);
            query.AddParameter("@abteilungid", abteilungid);
            query.Execute(true);
        }

		/// <summary>
		/// Only brings Personen that are in the schema. [prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]
		/// </summary>
		/// <param name="schemaid">schemaid</param>
		/// <returns>[prsID,name,nachname,abteilungid,abteilungname,prsschemaid,rgb]</returns>
		public static DataView GetPersonenInSchema(int schemaid)
		{
			Querry query = new Querry();
			query.SetSTPName("get_personeninschema");
			query.AddParameter("@schemaid", schemaid);
			return query.ExecuteForDataView();
		}

		static public bool CheckIfPlanExists(int monatID, int schemaID, int jahrID)
		{
			bool exists = false;

			Querry stp = new Querry();
			stp.SetSTPName("chk_abteilungverplant");
			stp.AddParameter("@monatID", monatID);
			stp.AddParameter("@schemaID", schemaID);
			stp.AddParameter("@jahrID", jahrID);
			DataView dv1 = stp.ExecuteForDataView();

			if (dv1.Count > 0)
			{
				if (dv1[0].Row[0].ToString() != "")
				{
					if (int.Parse(dv1[0].Row[0].ToString()) > 0)
					{
						exists = true;
					}
				}
			}
			return exists;
		}

		/// <summary>
		/// Creates a new ArbeitsPlan [planid]
		/// </summary>
		/// <param name="planname">planname</param>
		/// <param name="arbeitstageid">atid</param>
		/// <param name="schemaid">schemaid</param>
		/// <param name="continuous">Soll das Schema fortlaufend erstellt werden?</param>
		/// <returns>[planid]</returns>
		public static int CreateNewPlan(string planname, int arbeitstageid, int schemaid, bool continuous)
		{
			Querry query = new Querry();

			if(!continuous)
				query.SetSTPName("set_neuplan");
			else
				query.SetSTPName("set_newshemaplan");

			query.AddParameter("@planname", planname);
			query.AddParameter("@atid", arbeitstageid);
			query.AddParameter("@schemaid", schemaid);

			DataView dv = query.ExecuteForDataView(true);

            int retVal = 0;
			if (dv.Count > 0)
			{
				if (dv[0].Row[0].ToString() != "")
				{
                    try
                    {
                        retVal = int.Parse(dv[0].Row[0].ToString());
                    }
                    catch (InvalidCastException e)
                    {
                        Gencode.Common.Util.ErrorHandler.LoggError("Create plan returned no planid; Option:ContinuousSchema = " + continuous.ToString(), "PlanData.CreateNewPlan(string planname, int arbeitstageid, int schemaid, bool continuous)", e.Message);
                        Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, "Create plan returned no planid; Option:ContinuousSchema = " + continuous.ToString());
                    }
				}
			}

			return retVal;
		}

		static public bool CheckIfOpenYearExists()
		{
			bool check = false;
			DataView dv = SimpleQuerry.ExecuteForDataView("select JahrID, Jahr from PlanungsJahr where Status = 1");

			if (dv.Count > 0 && dv[0].Row[0].ToString() != "")
			{
				check = true;
			}

			return check;
		}

		public static int GetMaxYear()
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select max(Jahr) from PlanungsJahr");

			if (dv.Count > 0 && dv[0].Row[0].ToString() != "")
				return int.Parse(dv[0].Row[0].ToString());
			
			return 0;
		}

		/// <summary>
		/// Saves a new Month in the Database
		/// </summary>
		/// <param name="year"></param>
		/// <param name="month"></param>
		/// <param name="name"></param>
		/// <param name="von"></param>
		/// <param name="bis"></param>
		public static void SaveMonth(int year, int month, string name, string von, string bis, string mandid)
		{
			Querry stp = new Querry("set_arbeitstage");

			stp.AddParameter("@jahr", year);
			stp.AddParameter("@name", name);
			stp.AddParameter("@typwert", month);
			stp.AddParameter("@von", von);
			stp.AddParameter("@bis", bis);
			stp.AddParameter("@typ", 1); //1: Monat   2: Feiertag
			stp.AddParameter("@tage", 0);
			stp.AddParameter("@mandid", mandid);

			stp.Execute(true);
		}

		/// <summary>
		/// [von]
		/// </summary>
		/// <param name="planID">planid</param>
		/// <returns>[von]</returns>
		public static DataView GetFeiertageInPlan(int planID)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select Von,Bis,JahrID from ArbeitsPlan where PlanID = " + planID);
			string ex = "select Von from ArbeitsTage where typ = 2 and Von >= '" + dv[0].Row[0].ToString() + "' and Von <= '" + dv[0].Row[1].ToString() + "' and JahrID = " + dv[0].Row[2].ToString() + " order by Von";
			return SimpleQuerry.ExecuteForDataView(ex);
		}

		/// <summary>
		/// [Von,Bis]
		/// </summary>
		/// <param name="planid">planid</param>
		/// <returns>[Von,Bis]</returns>
		public static DataView GetPlanDate(int planid)
		{
			return SimpleQuerry.ExecuteForDataView("select Von,Bis from ArbeitsPlan where PlanID = " + planid);
		}

		public static int GetDienstCountInPlanPerDay(int dienstid, int planid, int abteilungid, string date)
		{
			int retVal = 0;
			DataView dv = SimpleQuerry.ExecuteForDataView("select COUNT(*) from Dienstplan where PlanID = " + planid + " and DienstID = " + dienstid + " and AbteilungID = " + abteilungid + " and Datum = CONVERT(datetime,'" + DateUtil.ConvertDateToSQL(date) + "',112)");
			try
			{
				retVal = int.Parse(dv[0].Row[0].ToString());
			}
			catch { }
			return retVal;
		}

		public static int GetDienstCountInPlanPerDay(int dienstid, int planid, int abteilungid, string date, int prsid)
		{
			int retVal = 0;
			DataView dv = SimpleQuerry.ExecuteForDataView("select COUNT(*) from Dienstplan where PlanID = " + planid + " and DienstID = " + dienstid + " and AbteilungID = " + abteilungid + " and Datum = CONVERT(datetime,'" + DateUtil.ConvertDateToSQL(date) + "',112) and PrsID = " + prsid);
			try
			{
				retVal = int.Parse(dv[0].Row[0].ToString());
			}
			catch { }
			return retVal;
		}

		public static int GetDienstCountInPlanPerDay(int planid, int abteilungid, string date, int prsid)
		{
			int retVal = 0;
			DataView dv = SimpleQuerry.ExecuteForDataView("select COUNT(*) from Dienstplan where PlanID = " + planid + " and AbteilungID = " + abteilungid + " and Datum = CONVERT(datetime,'" + DateUtil.ConvertDateToSQL(date) + "',112) and PrsID = " + prsid);
			try
			{
				retVal = int.Parse(dv[0].Row[0].ToString());
			}
			catch { }
			return retVal;
		}

		public static DataTable GetPersonenWithoutDienst(int planid, int abteilungid, string date)
		{
			Querry query = new Querry("get_personenwithoutdienst");
			query.AddParameter("@planid", planid);
			query.AddParameter("@abteilungid", abteilungid);
			query.AddParameter("@date", DateUtil.ConvertDateToSQL(date));

			return query.ExecuteForDataTable();// ExecuteForDataView(true);
		}

		public static DataView GetPersonenWithoutDienstDv(int planid, int abteilungid, string date)
		{
			Querry query = new Querry("get_personenwithoutdienst");
			query.AddParameter("@planid", planid);
			query.AddParameter("@abteilungid", abteilungid);
			query.AddParameter("@date", DateUtil.ConvertDateToSQL(date));

			return query.ExecuteForDataView();// ExecuteForDataView(true);
		}

		public static int GetDienstCountInSchema(int schemaid)
		{
			int retVal = 0;
			try
			{
				retVal = int.Parse(SimpleQuerry.ExecuteForDataView("select COUNT(*) from DienstSchema where SchemaID = " + schemaid)[0].Row[0].ToString());
			}
			catch (InvalidCastException)
			{
			}

			return retVal;
		}
		
    }
}
